﻿select * from [tb_Order_Buy_Detail]
select * from [tb_Order_Buy]
select * from [tb_Import]
select * from [tb_Branch]
select * from [tb_Job]
SELECT * FROM [tb_Login]
SELECT * FROM [tb_Employee]
SELECT * FROM [tb_Warehouse]
SELECT * FROM [tb_Room]
SELECT * FROM [tb_Warehouse_Detail]
SELECT * FROM [tb_Room_Detail]
insert into [tb_Warehouse_Detail](Warehouse_Id,[User_Id])Values(5,2)
insert into [tb_Room_Detail](Room_Id,[User_Id])Values(5,1)
-------------Login-----------
CREATE PROC sp_tb_Login_User
@UserName nvarchar(50),
@Passwords nvarchar(50)
AS
SELECT ROW_NUMBER() OVER(ORDER BY A.Users_Id ASC) AS Rows_ID,
A.UserName,A.Passwords,A.Employee_Id,A.USer_Detail_Id,
A.IsActive,A.IsUsed
FROM [tb_Login_User] A
WHERE 
-----------------------------
CREATE PROC sp_tb_Order_Buy_Import
@Id int
AS
DELETE FROM [tb_Room_Detail_Inport] WHERE Id = @Id
-----------------------------
ALTER PROC sp_tb_Order_Buy_Detail --25
@Order_Buy_Id int
AS
SELECT ROW_NUMBER() OVER(ORDER BY A.Order_Buy_Id ASC) AS Rows_ID,
A.Order_Buy_Id,A.Order_Id,A.Order_Buy_Code,A.Order_Buy_DateBegin,
A.Order_Buy_DateEnd,A.Order_Buy_Tax_Code,A.Order_Buy_Description,
A.Order_Buy_Payment_Type,A.Order_Buy_Uesr_ID,A.Customer_ID,A.IsActive,

B.Order_Buy_Detail_Id,B.Order_Buy_Id,B.Materials_Id,B.Order_Detail_Quality,
B.Order_Detail_Price,B.Order_Detail_Price,B.Order_Detail_CK_Percent,
B.Order_Detail_CK,B.Order_Detail_VAT_Percent,B.Order_Detail_VAT,
B.Order_Detail_VAT,B.Order_Detail_Total_Price,B.IsActive,

CUS.Customer_Alias,CUS.Customer_Name,C.Materials_Name
FROM [tb_Order_Buy] A
LEFT JOIN [tb_Order_Buy_Detail] B ON A.Order_Buy_Id = B.Order_Buy_Id
LEFT JOIN [tb_Materials] C ON C.Materials_Id = B.Materials_Id
LEFT JOIN [tb_Customer] CUS ON CUS.Customer_ID = A.Customer_ID
WHERE A.Order_Buy_Id = @Order_Buy_Id
-----------------------------
ALTER PROC sp_tb_Order_Del
@Order_Id int
AS
DELETE FROM [tb_Order_Detail] WHERE Order_Id = @Order_Id
DELETE FROM [tb_Order] WHERE Order_Id = @Order_Id
---------------------------------
ALTER PROC sp_tb_Order_Detail_Order
@Order_Id int
AS
SELECT ROW_NUMBER() OVER(ORDER BY A.Order_Detail_Id ASC) AS Rows_ID, 

A.Order_Detail_Id,A.Order_Id,A.Materials_Id,A.Order_Detail_Quality,
A.Order_Detail_Price,A.Order_Detail_CK_Percent,A.Order_Detail_CK,
A.Order_Detail_VAT_Percent,A.Order_Detail_VAT,A.Order_Detail_Total_Price,
A.Order_Detail_Note,A.IsActive,

B.Materials_Alias,B.Materials_Name,D.Customer_ID,D.Customer_Alias,D.Customer_Name,
D.Customer_Address
FROM [tb_Order_Detail] A
LEFT JOIN [tb_Materials] B ON A.Materials_Id = B.Materials_Id
LEFT JOIN [tb_Order] C ON C.Order_Id = A.Order_Id
LEFT JOIN [tb_Customer] D ON D.Customer_ID = C.Customer_Id
WHERE A.Order_Id = @Order_Id

---------------------------------
CREATE PROC sp_tb_Order_Buy_Of_Order 76
@Order_Id int
AS
SELECT A.Order_Detail_Id,A.Order_Id,
B.Materials_Alias,B.Materials_Name,
A.Materials_Id,A.Order_Detail_Quality,
A.Order_Detail_Price,A.Order_Detail_CK_Percent,A.Order_Detail_CK,
A.Order_Detail_VAT_Percent,A.Order_Detail_VAT,A.Order_Detail_Total_Price,
A.Order_Detail_Note


FROM [tb_Order_Detail] A
LEFT JOIN [tb_Materials] B ON A.Materials_Id = B.Materials_Id
WHERE A.Order_Id = @Order_Id
-----------MUA-HANG--------------
CREATE PROC sp_tb_Order_Buy_Delete
@Order_Buy_Id int
AS BEGIN
	DELETE FROM tb_Order_Buy_Detail WHERE Order_Buy_Id = @Order_Buy_Id
	DELETE FROM tb_Order_Buy WHERE Order_Buy_Id = @Order_Buy_Id
END
---------------------------------
CREATE PROC sp_tb_Order_Buy_Order
AS
SELECT ROW_NUMBER() OVER(ORDER BY A.Order_Id ASC) AS Rows_ID, 
A.Order_Id,A.Order_Code,A.Order_Date,
A.Customer_Id,A.Order_DateBegin,A.Order_DateEnd,
A.Order_Payment_Date,A.Order_Transport,
A.Order_Note,A.Order_VAT,A.Order_Total,
A.Order_Total_Payment,A.IsActive,

B.Customer_Name,B.Customer_Address,B.Customer_Alias
FROM [tb_Order] A
LEFT JOIN [tb_Customer] B ON A.Customer_Id = B.Customer_ID
--LEFT JOIN [tb_Order_Detail] C ON A.Order_Id = C.Order_Id
WHERE A.IsActive = 1
---------Đơn-Mua-Hàng--------
CREATE PROC sp_tb_tb_Order_Buy_Detail_Insert
@Order_Buy_Id int,
@Materials_Id int,
@Order_Detail_Quality int,
@Order_Detail_Price float,

@Order_Detail_CK_Percent float,
@Order_Detail_CK float,
@Order_Detail_VAT_Percent float,
@Order_Detail_VAT float,

@Order_Detail_Total_Price float,
@Order_Detail_Note nvarchar(500) = null,
@IsActive bit
AS
INSERT INTO [tb_Order_Buy_Detail](Order_Buy_Id,Materials_Id,Order_Detail_Quality,
Order_Detail_Price,

Order_Detail_CK_Percent,
Order_Detail_CK,
Order_Detail_VAT_Percent,
Order_Detail_VAT,

Order_Detail_Total_Price,IsActive)
VALUES(@Order_Buy_Id,@Materials_Id,
@Order_Detail_Quality,
@Order_Detail_Price,

@Order_Detail_CK_Percent,
@Order_Detail_CK,
@Order_Detail_VAT_Percent,
@Order_Detail_VAT,

@Order_Detail_Total_Price,@IsActive)
GO
-----------------------------
CREATE PROC sp_tb_Order_Buy
@KEYWORDS nvarchar(100) =''
AS
SELECT ROW_NUMBER() OVER(ORDER BY A.Order_Buy_Id ASC) AS Rows_ID, 
A.Order_Buy_Id,A.Order_Id,
A.Order_Buy_Code,
A.Order_Buy_DateBegin,
A.Order_Buy_DateEnd,
A.Customer_ID,
A.Order_Buy_Tax_Code,
A.Order_Buy_Description,
A.Order_Buy_Payment_Type,
A.Order_Buy_Uesr_ID,
A.IsActive,

B.Customer_Name,
B.Customer_Address,
B.Customer_Alias
FROM [tb_Order_Buy] A
LEFT JOIN [tb_Customer] B ON A.Customer_Id = B.Customer_ID
WHERE dbo.fnChuyenKhongDau(A.Order_Buy_Code) like '%'+@KEYWORDS+'%' 
OR A.Order_Buy_Code like '%'+@KEYWORDS+'%'
OR B.Customer_Name like '%'+@KEYWORDS+'%'
GO
---------Đơn-Mua-Hàng--------
CREATE PROC sp_tb_Order_Buy_Edit
@Order_Buy_Id int
AS
SELECT ROW_NUMBER() OVER(ORDER BY A.Order_Buy_Id ASC) AS Rows_ID, 
A.Order_Buy_Id,A.Order_Id,
A.Order_Buy_Code,
A.Order_Buy_DateBegin,
A.Order_Buy_DateEnd,
A.Customer_ID,
A.Order_Buy_Tax_Code,
A.Order_Buy_Description,
A.Order_Buy_Payment_Type,
A.Order_Buy_Uesr_ID,
A.IsActive,

B.Customer_Name,
B.Customer_Address,
B.Customer_Alias
FROM [tb_Order_Buy] A
LEFT JOIN [tb_Customer] B ON A.Customer_Id = B.Customer_ID
WHERE Order_Buy_Id = @Order_Buy_Id
GO
------Đơn-Thêm-Mua-Hàng------
ALTER PROC tb_Order_Buy_Insert --1,Null,'2014-08-22 00:00:00.000','2014-08-22 00:00:00.000',null,
--null,null,1,1
@Order_Id int,
@Order_Buy_Code nvarchar(50) = NULL,
@Order_Buy_DateBegin Datetime,
@Order_Buy_DateEnd Datetime,
@Order_Buy_Tax_Code nvarchar(255) = NULL,
@Order_Buy_Description nvarchar(MAX) = NULL,
@Order_Buy_Payment_Type nvarchar(MAX) = NULL,
@Order_Buy_Uesr_ID int,
@Customer_ID int,
@Order_Buy_Id int output
AS
INSERT INTO [tb_Order_Buy](Order_Id,
Order_Buy_Code,
Order_Buy_DateBegin,
Order_Buy_DateEnd,
Order_Buy_Tax_Code,
Order_Buy_Description,
Order_Buy_Payment_Type,
Order_Buy_Uesr_ID,
Customer_ID)

VALUES(@Order_Id,
@Order_Buy_Code,
@Order_Buy_DateBegin,
@Order_Buy_DateEnd,
@Order_Buy_Tax_Code,
@Order_Buy_Description,
@Order_Buy_Payment_Type,
@Order_Buy_Uesr_ID,
@Customer_ID)
SET @Order_Buy_Id =SCOPE_IDENTITY()
RETURN  @Order_Buy_Id
GO
---------Đơn-Đặt-Hàng--------
ALTER PROC sp_tb_Order_Search
@KEYWORDS nvarchar(100) =''
AS
SELECT ROW_NUMBER() OVER(ORDER BY A.Order_Id ASC) AS Rows_ID, 
A.Order_Id,A.Order_Code,A.Order_Date,
A.Customer_Id,A.Order_DateBegin,A.Order_DateEnd,
A.Order_Payment_Date,A.Order_Transport,
A.Order_Note,A.Order_VAT,A.Order_Total,
A.Order_Total_Payment,A.IsActive,

B.Customer_Name,B.Customer_Address,B.Customer_Alias
FROM [tb_Order] A
LEFT JOIN [tb_Customer] B ON A.Customer_Id = B.Customer_ID
--LEFT JOIN [tb_Order_Detail] C ON A.Order_Id = C.Order_Id
WHERE dbo.fnChuyenKhongDau(A.Order_Code) like '%'+@KEYWORDS+'%' 
OR A.Order_Code like '%'+@KEYWORDS+'%'
OR B.Customer_Name like '%'+@KEYWORDS+'%'
---------Đơn-Đặt-Hàng-Edit-------
CREATE PROC sp_tb_Order_Edit
@Order_Id int
AS
SELECT A.Order_Id,A.Order_Code,A.Order_Date,
A.Customer_Id,A.Order_DateBegin,A.Order_DateEnd,
A.Order_Payment_Date,A.Order_Transport,
A.Order_Note,A.Order_VAT,A.Order_Total,
A.Order_Total_Payment
FROM [tb_Order] A
WHERE A.Order_Id = @Order_Id
---------Đơn-Đặt-Hàng-Insert-------
--sp_tb_Order_Insert 'ABC','2014-08-12 00:00:00.000',1,'2014-08-12 00:00:00.000',
--'2014-08-12 00:00:00.000',0,NULL,0,NULL,0,0,1,1,1,1,NULL
ALTER PROC sp_tb_Order_Insert
@Order_Code nvarchar(50),
@Order_Date datetime = null,
@Customer_Id int = 0,
@Order_DateBegin datetime = null,
@Order_DateEnd datetime = null,
@Order_Payment_Date datetime = null,
@Order_Transport float = 0,
@Order_Note nvarchar(500) = null,
@Order_VAT float = 0,
@Order_Total float = 0,
@Order_Total_Payment float = 0,
@Order_Id int output,
@IsActive bit
AS 
BEGIN
	INSERT INTO tb_Order(Order_Code,
Order_Date,
Customer_Id,
Order_DateBegin,
Order_DateEnd,
Order_Payment_Date,
Order_Transport,
Order_Note,
Order_VAT,
Order_Total,
Order_Total_Payment,IsActive)
	VALUES(@Order_Code,
@Order_Date,
@Customer_Id,
@Order_DateBegin,
@Order_DateEnd,
@Order_Payment_Date,
@Order_Transport,
@Order_Note,
@Order_VAT,
@Order_Total,
@Order_Total_Payment,@IsActive)
SET @Order_Id =SCOPE_IDENTITY()
RETURN  @Order_Id

END
---------Đơn-Đặt-Hàng-Update-------
ALTER PROC sp_tb_Order_Update
@Order_Id int,
@Order_Code nvarchar(50),
@Order_Date datetime = null,
@Customer_Id int = 0,
@Order_DateBegin datetime = null,
@Order_DateEnd datetime = null,
@Order_Payment_Date datetime = null,
@Order_Transport float = 0,
@Order_Note nvarchar(500) = null,
@Order_VAT float = 0,
@Order_Total float = 0,
@Order_Total_Payment float = 0
AS BEGIN
	UPDATE tb_Order
	SET Order_Code = @Order_Code,
	Order_Date = @Order_Date,
	Customer_Id = @Customer_Id,
	Order_DateBegin = @Order_DateBegin,
	Order_DateEnd = @Order_DateEnd,
	Order_Payment_Date = @Order_Payment_Date,
	Order_Transport = @Order_Transport,
	Order_Note = @Order_Note,
	Order_VAT = @Order_VAT,
	Order_Total = @Order_Total,
	Order_Total_Payment = @Order_Total_Payment
	WHERE Order_Id = @Order_Id
END
---------Đơn-Đặt-Hàng-Delete-------
ALTER PROC sp_tb_Order_Delete
@Order_Id int
AS BEGIN
	DELETE FROM tb_Order_Detail WHERE Order_Id = @Order_Id
	DELETE FROM tb_Order WHERE Order_Id = @Order_Id
END

--BEGIN TRANSACTION
--DECLARE @DataID int;
--insert into [tb_Order](Order_Code)values('abc')
--SELECT @DataID = scope_identity();
--insert into [tb_Order_Detail](Order_Id)values(@DataID)
--COMMIT

ALTER PROC sp_tb_Order_Detail_Insert
@Order_Id int,
@Materials_Id int,
@Order_Detail_Quality int,
@Order_Detail_Price float,

@Order_Detail_CK_Percent float,
@Order_Detail_CK float,
@Order_Detail_VAT_Percent float,
@Order_Detail_VAT float,

@Order_Detail_Total_Price float,
@Order_Detail_Note nvarchar(500) = null,
@IsActive bit
AS
INSERT INTO [tb_Order_Detail](Order_Id,Materials_Id,Order_Detail_Quality,
Order_Detail_Price,

Order_Detail_CK_Percent,
Order_Detail_CK,
Order_Detail_VAT_Percent,
Order_Detail_VAT,

Order_Detail_Total_Price,Order_Detail_Note,IsActive)
VALUES(@Order_Id,@Materials_Id,
@Order_Detail_Quality,
@Order_Detail_Price,

@Order_Detail_CK_Percent,
@Order_Detail_CK,
@Order_Detail_VAT_Percent,
@Order_Detail_VAT,

@Order_Detail_Total_Price,@Order_Detail_Note,@IsActive)
GO
-------------------------------
ALTER PROC sp_tb_Order_Detail_Edit
@Order_Id INT
AS
SELECT ROW_NUMBER() OVER(ORDER BY A.Order_Detail_Id ASC) AS Rows_ID,
C.Materials_Alias,C.Materials_Name,

A.Order_Detail_CK_Percent,A.Order_Detail_CK,A.Order_Detail_VAT_Percent,
A.Order_Detail_VAT,A.Materials_Id,
A.Order_Detail_Id,A.Order_Detail_Quality,A.Order_Detail_Price,A.Order_Detail_Total_Price,
A.Order_Detail_Note,A.IsActive,

D.Customer_Alias,D.Customer_Name,B.Order_Code,B.Order_DateBegin
FROM [tb_Order_Detail] A
LEFT JOIN [tb_Order] B ON A.Order_Id = B.Order_Id
LEFT JOIN [tb_Materials] C ON A.Materials_Id = C.Materials_Id
LEFT JOIN [tb_Customer] D ON B.Customer_Id = D.Customer_ID
WHERE B.Order_Id = @Order_Id

-------------------------------
ALTER PROC sp_tb_Order_Detail_Update
@Order_Detail_Id INT,
@Order_Detail_Quality INT,
@Order_Detail_Price FLOAT,
@Order_Detail_CK_Percent FLOAT,
@Order_Detail_CK FLOAT,
@Order_Detail_VAT_Percent FLOAT,
@Order_Detail_VAT FLOAT,
@Order_Detail_Total_Price FLOAT
AS
UPDATE [tb_Order_Detail] SET Order_Detail_Quality = @Order_Detail_Quality,
Order_Detail_Price = @Order_Detail_Price,
Order_Detail_CK_Percent = @Order_Detail_CK_Percent,
Order_Detail_CK = @Order_Detail_CK,
Order_Detail_VAT_Percent = @Order_Detail_VAT_Percent,
Order_Detail_VAT = @Order_Detail_VAT,
Order_Detail_Total_Price = @Order_Detail_Total_Price
WHERE Order_Detail_Id = @Order_Detail_Id
---------KHÁCH-HÀNG--------
CREATE PROC sp_tb_Customer_Search
@KEYWORDS nvarchar(100) =''
AS
SELECT ROW_NUMBER() OVER(ORDER BY A.Customer_ID ASC) AS Rows_ID,
A.Customer_ID,A.Customer_Alias,A.Customer_Name
FROM [tb_Customer] A
WHERE A.Customer_Alias like '%'+@KEYWORDS+'%'
OR dbo.fnChuyenKhongDau(A.Customer_Name) like '%'+@KEYWORDS+'%'
OR A.Customer_Name like '%'+@KEYWORDS+'%'

-----------Order_Detail_Temp------------
ALTER PROC sp_tb_Order_Detail
AS
SELECT ROW_NUMBER() OVER(ORDER BY A.Order_Detail_Id ASC) AS Rows_ID,
A.Order_Detail_Id,A.Materials_Id,A.Order_Detail_Quality,A.Order_Detail_Price,
A.Order_Detail_Total_Price,A.Order_Detail_Note,
A.Order_Detail_CK_Percent,A.Order_Detail_CK,
A.Order_Detail_VAT_Percent,A.Order_Detail_VAT,


B.Materials_Name
FROM [tb_Order_Detail_Temp] A
LEFT JOIN [tb_Materials] B ON A.Materials_Id = B.Materials_Id
-------Order_Detail_Temp-Insert----------
ALTER PROC sp_tb_Order_Detail_Temp_Insert
@Materials_Id int,
@Order_Detail_Quality int,
@Order_Detail_Price float,
@Order_Detail_Total_Price float,

@Order_Detail_CK_Percent float,
@Order_Detail_CK float,
@Order_Detail_VAT_Percent float,
@Order_Detail_VAT float,

@Order_Detail_Note nvarchar(500) = null
AS
INSERT INTO [tb_Order_Detail_Temp](Materials_Id,Order_Detail_Quality,
Order_Detail_Price,Order_Detail_Total_Price,
Order_Detail_CK_Percent,
Order_Detail_CK ,
Order_Detail_VAT_Percent,
Order_Detail_VAT,

Order_Detail_Note)

VALUES(@Materials_Id,@Order_Detail_Quality,@Order_Detail_Price,
@Order_Detail_Total_Price,
@Order_Detail_CK_Percent,
@Order_Detail_CK,
@Order_Detail_VAT_Percent,
@Order_Detail_VAT,
@Order_Detail_Note)
-------Order_Detail_Temp-Update----------
ALTER PROC sp_tb_Order_Detail_Temp_Update
@Order_Detail_Id int,
@Order_Detail_Quality int,
@Order_Detail_Price float,
@Order_Detail_Total_Price float,
@Order_Detail_CK_Percent float =0,
@Order_Detail_CK float = 0,
@Order_Detail_VAT_Percent float = 0,
@Order_Detail_VAT float = 0,
@Order_Detail_Note nvarchar(500) = null
AS
UPDATE [tb_Order_Detail_Temp] SET 
Order_Detail_Quality = @Order_Detail_Quality,
Order_Detail_Price = @Order_Detail_Price,
Order_Detail_Total_Price = @Order_Detail_Total_Price,
Order_Detail_CK_Percent = @Order_Detail_CK_Percent,
Order_Detail_CK = @Order_Detail_CK,
Order_Detail_VAT_Percent = @Order_Detail_VAT_Percent,
Order_Detail_VAT = @Order_Detail_VAT,
Order_Detail_Note = @Order_Detail_Note
WHERE Order_Detail_Id = @Order_Detail_Id
-------Order_Detail_Temp-DELETE----------
CREATE PROC sp_tb_Order_Detail_Temp_Delete
@Order_Detail_Id int
AS
DELETE FROM [tb_Order_Detail_Temp] WHERE Order_Detail_Id = @Order_Detail_Id
-------Order_Detail_Temp-DELETE----------
CREATE PROC sp_tb_Order_Detail_Temp_Delete_All
AS
DELETE FROM [tb_Order_Detail_Temp]

----ORDER-DETAIL-UPDATE-STT----
ALTER PROC sp_tb_Order_Detail_Stt
@Order_Id int,
@IsActive bit
AS
UPDATE  [tb_Order_Detail] SET IsActive = @IsActive
WHERE Order_Id = @Order_Id

UPDATE  [tb_Order] SET IsActive = @IsActive
WHERE Order_Id = @Order_Id